import mysql.connector
from mysql.connector import Error
from pyproj import Proj
import rasterio
def conn():
    try:
        mydb = mysql.connector.connect(
            host="192.168.0.236",
            user="root",
            passwd="Xzc@12345",
            database="mcdullx_dw"
        )
        return mydb
    except Error as e:
        print(f"Error connecting to MySQL: {e}")
        return None

def findOne(params, table_name):
    mydb = conn()
    road_info = {}
    if not mydb:
        return None,None
    try:
        mycursor = mydb.cursor()
        # fields = "road_code,date_time, co_pfl, hc_pfl, nox_pfl, pm25_pfl"
        fields = "road_code, road_lon,road_lat,date_time,nox_pfl, pm25_pfl"
        ins_sql = f"SELECT {fields} FROM {{}} WHERE station_code = %s AND date_time BETWEEN %s AND %s AND road_name = %s ORDER BY date_time ASC"
        sql = ins_sql.format(table_name)
        # print(f"Executing SQL: {sql} with params: {params}")
        mycursor.execute(sql, params)
        rs = mycursor.fetchall()
        road_info = map_to_time_dict(rs)
        return road_info
    except Error as e:
        print(f"Error executing query: {e}")
        return None,None
    finally:
        if mydb.is_connected():
            mycursor.close()
            mydb.close()

def map_to_time_dict(rs):
    result_dict = {}
    road_info = {}
    for row in rs:
        try:
            road_code = row[0]
            lon = float(row[1])
            lat = float(row[2])
            x,y = changeUTM(lat,lon)
            elevation = int(compute_elevation(lon, lat))
            road_info = {
                "road_code": road_code,
                "lon": lon,
                "lat": lat,
                "x": x,
                "y": y,
                "elevation": elevation
            }
            formatted_date_time = row[3].strftime('%Y-%m-%d %H:%M:%S')
            values = row[4:]
            result_dict[formatted_date_time] = {
                'nox': float(values[0]),
                'pm25': float(values[1]),
            }
        except (ValueError, IndexError) as e:
            print(f"Error processing row: {e}")
    road_info['emissions'] = result_dict
    return road_info

def changeUTM(lat,lon):
    #首先定义要转换的投影坐标系
    p1 = Proj(proj='utm',zone=50,ellps='WGS84')
    x,y = p1(lon,lat)  # 将地理坐标转换为投影坐标，地理坐标为WGS84下的坐标
    x= round(x/1000,3)
    y = round(y/1000,3)
    return x,y

def compute_elevation(lon, lat):
    elevation = 0
    # 打开SRTM数据文件
    with rasterio.open('sd-dem-90.tif') as dataset:
        # 获取经纬度对应的像素坐标
        row, col = dataset.index(lon, lat)
        # 读取高程数据
        elevation = dataset.read(1)[row, col]
    return elevation